package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.annotation.Resource;
import model.User;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

@Service("userDAO")
public class UserDAO implements IUserDAO{
	public static final String INSERT = "INSERT INTO USERS "
			+ "(USERNAME,PASSWORD,ADDRESS,BIRTHDAY,SEX,EMAIL,role) "
			+ "VALUES(?,?,?,?,?,?,?)";
	public static final String UPDATE = "UPDATE USERS "
			+ "SET USERNAME=?,PASSWORD=?,ROLE=?, BIRTHDAY=?,ADDRESS=?,SEX=?,EMAIL=? "
			+ "WHERE ID=?";
	public static final String DELETE = "DELETE FROM USERS " + "WHERE ID=?";

	public static final String ALL_USERS = "SELECT * " + "From USERS ";

	public static final String READ_BY_ID = "SELECT * " + "FROM USERS "
			+ "WHERE ID = ?";
	public static final String READ_BY_ROLE = "SELECT * " + "FROM USERS "
			+ "WHERE ROLE = ?";

	public static final String READ_BY_USERNAME = "SELECT * " + "FROM USERS "
			+ "WHERE USERNAME = ?";

	@Resource
	private JdbcTemplate jdbcTemplate;

	public void addUser(final String username, final String password,
			final String address, final String birthday, final String sex,
			final String email, final int role) {
		PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement pst = connection.prepareStatement(INSERT);
				pst.setString(1, username);
				pst.setString(2, password);
				pst.setString(3, address);
				pst.setString(4, birthday);
				pst.setString(5, sex);
				pst.setString(6, email);
				pst.setInt(7, role);
				return pst;
			}
		};
		jdbcTemplate.update(preparedStatementCreator);
	}

	public User getOneUser(final int id) {
		PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement pst = connection.prepareStatement(READ_BY_ID);
				pst.setInt(1, id);
				return pst;
			}
		};
		List<User> result = jdbcTemplate.query(preparedStatementCreator,
				new RowMapper<User>() {
					@Override
					public User mapRow(ResultSet resultSet, int i)
							throws SQLException {
						return new User(resultSet.getString("id"), resultSet
								.getString("username"), resultSet
								.getString("password"), resultSet
								.getString("address"), resultSet
								.getString("birthday"), resultSet
								.getString("sex"),
								resultSet.getString("email"), resultSet
										.getInt("role"));
					}
				});
		return result.get(0);
	}

	public List<User> getUserByRole(final String role) {
		PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement pst = connection
						.prepareStatement(READ_BY_ROLE);
				pst.setString(1, role);
				return pst;
			}
		};
		List<User> result = jdbcTemplate.query(preparedStatementCreator,
				new RowMapper<User>() {
					@Override
					public User mapRow(ResultSet resultSet, int i)
							throws SQLException {
						return new User(resultSet.getString("id"), resultSet
								.getString("username"), resultSet
								.getString("password"), resultSet
								.getString("address"), resultSet
								.getString("birthday"), resultSet
								.getString("sex"),resultSet
								.getString("email"), resultSet
								.getInt("role"));
					}
				});
		if (result.size() == 0) {
			return null;
		}
		return result;
	}

	public User getOneUser(final String username) {
		PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement pst = connection
						.prepareStatement(READ_BY_USERNAME);
				pst.setString(1, username);
				return pst;
			}
		};
		List<User> result = jdbcTemplate.query(preparedStatementCreator,
				new RowMapper<User>() {
					@Override
					public User mapRow(ResultSet resultSet, int i)
							throws SQLException {
						return new User(resultSet.getString("id"), resultSet
								.getString("username"), resultSet
								.getString("password"), resultSet
								.getString("address"), resultSet
								.getString("birthday"), resultSet
								.getString("sex"),
								resultSet.getString("email"), resultSet
										.getInt("role"));
					}
				});
		if (result.size() == 0) {
			return null;
		}
		return result.get(0);
	}

	public void deleteUser(final int id) {
		PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement pst = connection.prepareStatement(DELETE);
				pst.setInt(1, id);
				return pst;
			}
		};
		jdbcTemplate.update(preparedStatementCreator);
	}

	public void updateUser(final String username, final String password,
			final int role, final String birthday, final String address,
			final String sex, final String email,final int id) {
		PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement pst = connection.prepareStatement(UPDATE);
				pst.setString(1, username);
				pst.setString(2, password);
				pst.setInt(3, role);
				pst.setString(4, birthday);
				pst.setString(5, address);
				pst.setString(6, sex);
				pst.setString(7, email);
				pst.setInt(8, id);
				return pst;
			}
		};
		jdbcTemplate.update(preparedStatementCreator);
	}

	public List<User> getAllUsers() {
		List<User> result = jdbcTemplate.query(ALL_USERS,
				new RowMapper<User>() {
					@Override
					public User mapRow(ResultSet resultSet, int i)
							throws SQLException {
						return new User(resultSet.getString("id"), resultSet
								.getString("username"), resultSet
								.getString("password"), resultSet
								.getString("address"), resultSet
								.getString("birthday"), resultSet
								.getString("sex"),
								resultSet.getString("email"), resultSet
										.getInt("role"));
					}
				});
		if (result.isEmpty())
			return null;
		return result;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
}
